﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

/// <summary>
///db_gold 的摘要说明
/// </summary>
public class db_gold
{
    SqlDBClass db;
	public db_gold()
	{
        //
        //TODO: 在此处添加构造函数逻辑
        //
        db = new SqlDBClass();
	}

    /// <summary>
    /// 金币信息搜索
    /// </summary>
    /// <param name="status_sell"></param>
    /// <param name="status_use"></param>
    /// <returns></returns>
    public DataSet selectGold_search(string status_sell, string status_use, string goldNum)
    {
        string strsql = "select ROW_NUMBER() Over(ORDER BY goldcardid) as num,goldcardid,pwd,gold,status_sell,status_use from goldcard ";
        bool flag = false;
        if (goldNum.Equals("请选择金币值"))
        {
            strsql += " where gold like '%%' ";
        }
        else
        {
            strsql += " where gold="+goldNum+" ";
        }
        
        if (!status_sell.Equals("all"))
        {
            strsql += " and status_sell='" + status_sell + "'";
            //flag = true;
        }
        if (!status_use.Equals("all"))
        {
            //if (flag)
            //{
                strsql += " and status_use='" + status_use + "'";
            //}
            //else
            //{
            //    strsql += " where status_use='" + status_use + "'";
            //}
        }
        return db.execSql(strsql);
    }

    /// <summary>
    /// 增加金币卡
    /// </summary>
    /// <param name="id"></param>
    /// <param name="pwd"></param>
    /// <param name="gold"></param>
    /// <returns></returns>
    public bool addGoldCard(string id, string pwd, string gold)
    {
        string strsql = "insert into goldcard(goldcardid,pwd,gold,status_sell,status_use) values('" + id + "','" + pwd + "','" + gold + "','0','0')";
        return db.execSqlNoQuery(strsql);
    }

    /// <summary>
    /// 批量增加金币卡
    /// </summary>
    /// <param name="arrSql">二维数组，[0][i]：id;[1][i]密码</param>
    /// <param name="gold">数值</param>
    /// <returns></returns>
    public bool addGoldCardList(string[][] arrSql,string gold)
    {
        string[] arrS=new string[arrSql[0].Length];
        for (int i = 0; i < arrSql[0].Length; i++)
        {
            arrS[i] = "insert into goldcard(goldcardid,pwd,gold,status_sell,status_use) values('" + arrSql[0][i] + "','" + arrSql[1][i] + "','" + gold + "','0','0')";
        }
        return db.execSqlTran(arrS);
    }

    /// <summary>
    /// 用户购买的金币卡
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public DataSet selectCardSell_userID(string id)
    {
        string strsql = "select gs.goldcardid,gs.[time],gc.gold from goldsell gs,goldcard gc where gs.goldcardid=gc.goldcardid and gc.status_sell='1' and gc.status_use='0' and gs.userID='" + id + "'";
        return db.execSql(strsql);
    }

    /// <summary>
    /// 根据卡号，查询卡值
    /// </summary>
    /// <param name="goldCardID"></param>
    /// <returns></returns>
    public DataSet selectCardGold(string goldCardID)
    {
        string strsql = "select goldcardid,gold from goldcard where goldcardid='" + goldCardID + "'";
        return db.execSql(strsql);

    }

    /// <summary>
    /// 该卡是否已被购买
    /// </summary>
    /// <param name="goldcardID"></param>
    /// <returns>false:未购买；true:已经购买</returns>
    public bool isCardSell(string goldcardID)
    {
        string strsql = "select status_sell from goldcard where goldcardid='" + goldcardID + "'";
        return db.execSql(strsql).Tables[0].Rows[0]["status_sell"].ToString().Equals("0") ? false : true;
    }

    /// <summary>
    /// 该卡是否已被使用
    /// </summary>
    /// <param name="goldcardID"></param>
    /// <returns>false:未使用；true:已经使用</returns>
    public bool isCardUse(string goldcardID) {
        string strsql = "select status_use from goldcard where goldcardid='" + goldcardID + "'";
        return db.execSql(strsql).Tables[0].Rows[0]["status_use"].ToString().Equals("0") ? false : true;
    }


    /// <summary>
    /// 出售金币卡
    /// </summary>
    /// <param name="cardID"></param>
    /// <param name="userID"></param>
    /// <returns></returns>
    public bool sellGoldCard(string cardID, string userID)
    {
        string[] str = new string[2];
        str[0] = "insert into goldsell(goldcardid,[time],userid) values('" + cardID + "','" + DateTime.Now + "'," + userID + ")";
        str[1] = "update goldcard set status_sell='1' where goldcardid='" + cardID + "'";
        return db.execSqlTran(str);
    }

    /*
     --插入usergold表
insert into usergold(userid,gold,goldCardid,addtime) values('1','','','')
--更新user表的gold值
update [user] set gold=gold+20 where username='arshow'
--更新goldcard表的使用状态
update goldcard set status_use='1' where goldcardid=''
     */

    /// <summary>
    /// 充值金币卡
    /// </summary>
    /// <param name="userid"></param>
    /// <param name="gold"></param>
    /// <param name="goldcardid"></param>
    /// <returns></returns>
    public bool topUpGoldCard(string userid, string gold, string goldcardid)
    {
        string[] str = new string[3];
        str[0] = "insert into usergold(userid,gold,goldCardid,addtime) values('" + userid + "'," + gold + ",'" + goldcardid + "','" + DateTime.Now + "')";
        str[1] = "update [user] set gold=gold+" + gold + " where userid=" + userid;
        str[2] = "update goldcard set status_use='1' where goldcardid='" + goldcardid + "'";
        return db.execSqlTran(str);
    }

    /// <summary>
    /// 验证卡号密码
    /// </summary>
    /// <param name="cardid"></param>
    /// <param name="pwd"></param>
    /// <returns>true：正确；false：错误</returns>
    public bool cardID_pwd(string cardid, string pwd)
    {
        string strsql = "select * from goldcard where goldcardid='" + cardid + "' and pwd='" + pwd + "'";
        return db.execSql(strsql).Tables[0].Rows.Count != 0;
    }

    /// <summary>
    /// 统计金币出售
    /// </summary>
    /// <param name="gold"></param>
    /// <param name="time1"></param>
    /// <param name="time2"></param>
    /// <returns></returns>
    public DataSet statisticsGoldSell(string gold, string time1, string time2)
    {
        string strsql = "select count(gs.goldcardid) as num from goldcard gc,goldsell gs where gc.goldcardid=gs.goldcardid and gold=" + gold + " and gs.[time] between '" + time1 + "' and DATEADD(day,1,'" + time2 + "') group by gold ";
        return db.execSql(strsql);
    }
    //select count(id) as num from usergold where gold=30 and usergold.[addtime] between '2012/02/15' and '2012/02/22' group by gold

    /// <summary>
    /// 统计金币充值
    /// </summary>
    /// <param name="gold"></param>
    /// <param name="time1"></param>
    /// <param name="time2"></param>
    /// <returns></returns>
    public DataSet statisticsGoldTopUp(string gold, string time1, string time2)
    {
        string strsql = "select count(id) as num from usergold where gold=" + gold + " and usergold.[addtime] between '" + time1 + "' and DATEADD(day,1,'" + time2 + "') group by gold";
        return db.execSql(strsql);
    }
}